﻿/*
 * copyright (c) 2009 hvbird
 *
 * This file is part of tdlib.
 *
 * tdlib is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * tdlib is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 */

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Xml;
using System.Text;
using System.Collections;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Win32;


namespace TDLib.DB
{
    /// <summary>
    /// 用于数据库访问的通用类
    /// </summary>
    public class MSSQLHelper
    {
        /// <summary>
        /// MS SQL Server连接类型
        /// </summary>
        public const string C_MSSQL = "MSSQL";

        /// <summary>
        /// OLE DB连接类型
        /// </summary>
        public const string C_OLEDB = "OLEDB";

        public const string C_VersionInfoPub = "1";
        public const string C_VersionKingOA = "2";
        public const string C_VersionKingCase = "3";
        public const string C_VersionKingMail = "4";
        public const string C_VersionKingOther = "5";

        public MSSQLHelper()
        { }

        #region RunSQLReturnDS functions

        /// <summary>
        /// 从数据库中执行某一查询，返回一个结果集(用指定的连接对象)
        /// </summary>
        public DataSet RunSQLReturnDS(string strSQL, IDbConnection conn)
        {
            IDbDataAdapter adapter = GetDataAdapter(strSQL, conn);

            OpenConnection(conn);

            DataSet ds = new DataSet("DataSet");
            adapter.Fill(ds);

            return ds;

        }

        public DataSet RunSQLReturnDSAndSchema(string strSQL, IDbConnection conn, System.Data.SchemaType schemaType)
        {
            IDbDataAdapter adapter = GetDataAdapter(strSQL, conn);

            OpenConnection(conn);

            DataSet ds = new DataSet("DataSet");
            adapter.FillSchema(ds, schemaType);
            adapter.Fill(ds);

            return ds;
        }
        /// <summary>
        /// 从数据库中执行某一IDbCommand，返回一个结果集(用指定的连接对象)
        /// </summary>
        public DataSet RunSQLReturnDS(IDbCommand cmd)
        {

            IDbDataAdapter adapter = GetDataAdapter(cmd.Connection);

            adapter.SelectCommand = cmd;

            DataSet ds = new DataSet("DataSet");
            OpenConnection(cmd.Connection);

            adapter.Fill(ds);

            return ds;
        }
        #endregion

        #region RunSQLReturnDR functions
        /// <summary>
        /// 从数据库中执行某一查询，返回一个IDataReader(用指定的连接对象)
        /// </summary>
        public IDataReader RunSQLReturnDR(string strSQL, IDbConnection conn, IDbTransaction ts)
        {
            IDbCommand cmd = GetDbCommand(strSQL, conn, ts);

            OpenConnection(conn);

            return cmd.ExecuteReader();
        }

        /// <summary>
        /// 从数据库中执行某一查询，返回一个IDataReader(用指定的连接对象)
        /// </summary>		
        public IDataReader RunSQLReturnDR(string strSQL, IDbConnection conn)
        {
            IDbCommand cmd = GetDbCommand(strSQL, conn);

            OpenConnection(conn);

            return cmd.ExecuteReader();
        }

        /// <summary>
        /// 从数据库中执行某一IDbCommand，返回一个IDataReader
        /// </summary>
        public IDataReader RunSQLReturnDR(IDbCommand cmd)
        {
            OpenConnection(cmd.Connection);

            return cmd.ExecuteReader();
        }

        /// <summary>
        /// 从数据库中执行某一IDbCommand，返回一个IDataReader
        /// </summary>
        public IDataReader RunSQLReturnDR(IDbCommand cmd, IDbTransaction ts)
        {
            OpenConnection(cmd.Connection);

            cmd.Transaction = ts;

            return cmd.ExecuteReader();
        }
        #endregion

        #region RunSQLExecuteScalar functions
        /// <summary>
        /// 从数据库中执行某一查询，返回第一行，第一列
        /// </summary>
        public object RunSQLExecuteScalar(string strSQL)
        {
            InitData();
            return RunSQLExecuteScalar(strSQL, _Default_Key);
        }

        /// <summary>
        /// 从数据库中执行某一查询，返回返回第一行，第一列(用指定的连接名)
        /// </summary>
        public object RunSQLExecuteScalar(string strSQL, string strConnName)
        {
            object objResult;
            InitData();
            IDbConnection conn = GetConnection(strConnName);
            objResult = RunSQLExecuteScalar(strSQL, conn);
            conn.Close();

            return objResult;

        }

        /// <summary>
        /// 从数据库中执行某一查询，返回返回第一行，第一列(用指定的连接对象)
        /// </summary>
        public object RunSQLExecuteScalar(string strSQL, IDbConnection conn, IDbTransaction ts)
        {
            IDbCommand cmd = GetDbCommand(strSQL, conn, ts);

            OpenConnection(conn);

            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 从数据库中执行某一查询，返回返回第一行，第一列(用指定的连接对象)
        /// </summary>
        public object RunSQLExecuteScalar(string strSQL, IDbConnection conn)
        {
            IDbCommand cmd = GetDbCommand(strSQL, conn);

            OpenConnection(conn);

            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 从数据库中执行某一IDbCommand，返回返回第一行，第一列
        /// </summary>
        public object RunSQLExecuteScalar(IDbCommand cmd)
        {
            OpenConnection(cmd.Connection);

            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 从数据库中执行某一IDbCommand，返回返回第一行，第一列
        /// </summary>
        public object RunSQLExecuteScalar(IDbCommand cmd, IDbTransaction ts)
        {
            OpenConnection(cmd.Connection);

            cmd.Transaction = ts;

            return cmd.ExecuteScalar();
        }

        #endregion

        #region RunSQL functions
        /// <summary>
        /// 从数据库中执行某一SQL命令，不返回数据集合
        /// </summary>
        public int RunSQL(string strSQL)
        {
            InitData();
            return RunSQL(strSQL, _Default_Key);
        }

        /// <summary>
        /// 从数据库中执行某一SQL命令，不返回数据集合(用指定的连接名)
        /// </summary>
        public int RunSQL(string strSQL, string strConnName)
        {
            int intResult = 0;
            InitData();

            IDbConnection conn = GetConnection(strConnName);

            intResult = RunSQL(strSQL, conn);

            conn.Close();

            return intResult;

        }


        /// <summary>
        /// 执行SQL命令，不返回数据集合
        /// </summary>
        public int RunSQL(string strSQL, IDbConnection conn)
        {
            return RunSQL(strSQL, conn, null);
        }

        /// <summary>
        /// 执行SQL命令，不返回数据集合
        /// </summary>
        public int RunSQL(string strSQL, IDbConnection conn, IDbTransaction ts)
        {
            IDbCommand cmd = GetDbCommand(strSQL, conn, ts);

            return RunSQL(cmd, ts);
        }

        /// <summary>
        /// 从数据库中执行某一IDbCommand，不返回数据集合
        /// </summary>
        public int RunSQL(IDbCommand cmd)
        {
            return RunSQL(cmd, null);
        }

        /// <summary>
        /// 从数据库中执行某一IDbCommand，不返回数据集合
        /// </summary>
        public int RunSQL(IDbCommand cmd, IDbTransaction ts)
        {
            OpenConnection(cmd.Connection);
            cmd.Transaction = ts;

            return cmd.ExecuteNonQuery();
        }
        #endregion

        #region GetDataAdapter functions
        /// <summary>
        /// 得到DataAdapter对象
        /// </summary>
        public IDbDataAdapter GetDataAdapter(string strSQL, IDbConnection conn)
        {
            IDbDataAdapter adapter = null;

            if (strSQL == string.Empty)
            {
                if (conn is SqlConnection)
                    adapter = new SqlDataAdapter();
                else
                    adapter = new OleDbDataAdapter();
            }
            else
            {
                if (conn is SqlConnection)
                    adapter = new SqlDataAdapter(strSQL, conn as SqlConnection);
                else
                    adapter = new OleDbDataAdapter(strSQL, conn as OleDbConnection);
            }
            return adapter;
        }

        /// <summary>
        /// 得到DataAdapter对象
        /// </summary>
        public IDbDataAdapter GetDataAdapter(IDbConnection conn)
        {
            return GetDataAdapter(string.Empty, conn);
        }
        #endregion

        #region GetDbCommand functions
        /// <summary>
        /// 得到IDbCommand对象
        /// </summary>
        public IDbCommand GetDbCommand(string strCmdText, IDbConnection conn)
        {
            IDbCommand cmd = null;

            if (conn is SqlConnection)
                cmd = new SqlCommand(strCmdText, conn as SqlConnection);
            else
                cmd = new OleDbCommand(strCmdText, conn as OleDbConnection);

            return cmd;
        }

        /// <summary>
        /// 得到IDbCommand对象
        /// </summary>
        public IDbCommand GetDbCommand(string strCmdText, IDbConnection conn, IDbTransaction ts)
        {
            IDbCommand cmd = null;

            if (conn is SqlConnection)
                cmd = new SqlCommand(strCmdText, conn as SqlConnection);
            else
                cmd = new OleDbCommand(strCmdText, conn as OleDbConnection);

            cmd.Transaction = ts;

            return cmd;
        }

        #endregion

        #region IDbConnection

        /// <summary>
        /// 指定sql连接串
        /// </summary>
        /// <param name="connstr"></param>
        public void SetConnString(string connstr)
        {
            _Str_ConnectionNode = connstr;
        }

        /// <summary>
        /// 根据连接标识得到数据库连接接口
        /// </summary>
        public IDbConnection GetConnection()
        {
            InitData();
            return GetConnection(_Default_Key);
        }

        /// <summary>
        /// 根据连接标识得到数据库连接接口
        /// </summary>
        public IDbConnection GetConnection(string strConnName)
        {
            IDbConnection conn = null;
            conn = new SqlConnection(GetConnString(strConnName));
            return conn;
        }

        /// <summary>
        /// 从指定的连接字符串得到数据库连接接口
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public IDbConnection GetConnectionFromString(string sqlstr)
        {
            IDbConnection conn = null;
            conn = new SqlConnection(sqlstr);
            return conn;
        }
        /// <summary>
        /// 如果连接对象是关闭的，那么打开连接对象
        /// </summary>
        public void OpenConnection(IDbConnection conn)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
        }

        /// <summary>
        /// 从配置文件中得到数据库连接串
        /// </summary>
        private string GetConnString(string strConnName)
        {
            _Default_Key = strConnName;
            InitData();
            return _Str_ConnectionNode;
        }

        /// <summary>
        /// 从配置文件中得到数据库连接串
        /// </summary>
        private string GetConnString()
        {
            InitData();
            return GetConnString(_Default_Key).ToString();
        }

        /// <summary>
        /// 初始化数据
        /// </summary>
        private void InitData()
        {
            if(_Default_Key.Length>0)
            {
                System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
                _Str_ConnectionNode = configurationAppSettings.GetValue(_Default_Key, typeof(string)).ToString();
            }
        }
            

        private string _Str_ConnectionNode = string.Empty;

        private static string _Default_Key = "";

        #endregion
    }
}

